﻿using Abp.Application.Services.Dto;
using Abp.Domain.Repositories;
using Abp.Linq.Extensions;
using Abp.UI;
using Camc.Quality.Bom;
using Camc.Quality.BomContrastive.BomContrastiveInterface;
using Camc.Quality.Bop;
using Camc.Quality.DataDictionary;
using Camc.Quality.Dto;
using Camc.Quality.Materials;
using Camc.Quality.Quality;
using Camc.Quality.RecordTable.FormClass;
using Camc.Quality.StoreManagement;
using Camc.Quality.TrepanningManager.CollectBom;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace Camc.Quality.BomContrastive
{
    public class BomContrastiveAppService : QualityAppServiceBase
    {
        private readonly IRepository<BopNode, Guid> _bopNodeRepository;
        private readonly IRepository<BopDesc, Guid> _bopDescRepository;
        private readonly IRepository<BopContent, Guid> _bopContentRepository;
        private readonly IRepository<TechniqueEntity, Guid> _techniqueEntityRepository;
        private readonly IRepository<ProcessesEntity, Guid> _processesEntityRepository;
        private readonly IRepository<WorkStepEntity, Guid> _workStepEntityRepository;
        private readonly IRepository<LoadPieceEntity, Guid> _loadPieceRepository;
        private readonly IRepository<MaterialsContent, Guid> _materialsContentrepository;
        private readonly IRepository<MaterialsNode, Guid> _materialsNoderepository;
        private readonly IRepository<BomDesign, Guid> _bomDesignrepository;
        private readonly IRepository<BomDesignDetail, Guid> _bomDesignDetailrepository;
        private readonly IRepository<Sys_Code, Guid> _sysCodeRepository;
        private readonly IBomContrastiveToExcel _bomContrastiveToExcel;
        private readonly BomContentAppService _bomContentAppService;
        private readonly StoreFunctionsAppService _storeFunctionsAppService;
        private readonly IRepository<CollectBomManagerChild, Guid> _collectBomManagerChildRepository;
        public BomContrastiveAppService(IRepository<BopNode, Guid> bopNodeRepository,
            IRepository<BopDesc, Guid> bopDescRepository,
            IRepository<BopContent, Guid> bopContentRepository,
            IRepository<TechniqueEntity, Guid> techniqueEntityRepository,
            IRepository<ProcessesEntity, Guid> processesEntityRepository,
            IRepository<WorkStepEntity, Guid> workStepEntityRepository,
            IRepository<LoadPieceEntity, Guid> loadPieceRepository,
            IRepository<MaterialsContent, Guid> materialsContentrepository,
            IRepository<MaterialsNode, Guid> materialsNoderepository,
            IRepository<BomDesign, Guid> bomDesignrepository,
            IRepository<BomDesignDetail, Guid> bomDesignDetailrepository,
            IRepository<Sys_Code, Guid> sysCodeRepository, IBomContrastiveToExcel bomContrastiveToExcel, 
            IRepository<CollectBomManagerChild, Guid> collectBomManagerChildRepository, StoreFunctionsAppService storeFunctionsAppService)
        {
            this._bopNodeRepository = bopNodeRepository;
            this._bopDescRepository = bopDescRepository;
            this._bopContentRepository = bopContentRepository;
            this._techniqueEntityRepository = techniqueEntityRepository;
            this._processesEntityRepository = processesEntityRepository;
            this._workStepEntityRepository = workStepEntityRepository;
            this._loadPieceRepository = loadPieceRepository;
            this._materialsContentrepository = materialsContentrepository;
            this._materialsNoderepository = materialsNoderepository;
            this._bomDesignrepository = bomDesignrepository;
            this._bomDesignDetailrepository = bomDesignDetailrepository;
            this._sysCodeRepository = sysCodeRepository;
            this._bomContrastiveToExcel = bomContrastiveToExcel;
            this._collectBomManagerChildRepository = collectBomManagerChildRepository;
            this._storeFunctionsAppService = storeFunctionsAppService;
        }
        ///// <summary>
        ///// 获取设计bom发次
        ///// </summary>
        ///// <param name="module"></param>
        ///// <returns></returns>
        //public List<BomContrastiveOtherDto> GetSendTimeData(string module) 
        //{
        //    if (string.IsNullOrEmpty(module)) 
        //    {
        //        var searchBomXJList = from bomDesignData in _bomDesignrepository.GetAll().Where(t => t.Module == module)
        //                              select new BomContrastiveOtherDto
        //                              {
        //                                  label = bomDesignData.SendTime,
        //                                  value = bomDesignData.Id
        //                              };
        //        return searchBomXJList.ToList();

        //    }
        //    return new List<BomContrastiveOtherDto>();
        //}

        /// <summary>
        /// 获取发次
        /// </summary>
        /// <returns></returns>
        public async Task<List<BomContrastiveOtherDto>> GetSendTime(string module)
        {
            var querySendTimeDatas = from querySendTimeData in _bomDesignrepository.GetAll().Where(t => t.Module == module)
                                     select new BomContrastiveOtherDto
                                     {
                                         label = querySendTimeData.SendTime,
                                         value = querySendTimeData.Id
                                     };
            if (querySendTimeDatas.Count() > 0)
            {
                return querySendTimeDatas.ToList();
            }
            return new List<BomContrastiveOtherDto>();
        }
        /// <summary>
        /// 获取芯级
        /// </summary>
        /// <returns></returns>
        public async Task<List<BomContrastiveOtherDto>> GetXj(GetBomAnalysisContentListInput input)
        {
            //根据型号、发次 获取芯级
            var queryXjDatas = from queryXjData in _bomDesignrepository.GetAll().Where(t => t.SendTime == input.SendTime && t.Module == input.Model)
                               join queryBomDesignDetailData in _bomDesignDetailrepository.GetAll().Where(t => t.ParentId == Guid.Empty && t.LevelNum == 2) on queryXjData.Id equals queryBomDesignDetailData.BomDesignId
                               join queryMaterialsData in _materialsContentrepository.GetAll() on queryBomDesignDetailData.MaterialId equals queryMaterialsData.Id
                               select new BomContrastiveOtherDto
                               {
                                   label = queryMaterialsData.MaterialName,
                                   value = queryBomDesignDetailData.Id
                               };
            if (queryXjDatas.Count() > 0)
            {
                return queryXjDatas.ToList();
            }
            return new List<BomContrastiveOtherDto>();
        }
        ///// <summary>
        ///// 获取芯级
        ///// </summary>
        ///// <param name="guid"></param>
        ///// <returns></returns>
        //public List<BomContrastiveOtherDto> GetXJData(Guid guid)
        //{
        //    if (guid !=Guid.Empty)
        //    {
        //        var searchBomXJList = from bomDesignDetailData in _bomDesignDetailrepository.GetAll().Where(t => t.Id == guid && t.ParentId==Guid.Empty)
        //                              join materilsContent in _materialsContentrepository.GetAll() on bomDesignDetailData.MaterialId equals materilsContent.Id
        //                              select new BomContrastiveOtherDto
        //                              {
        //                                  label = materilsContent.MaterialName,
        //                                  value = bomDesignDetailData.Id
        //                              };
        //        return searchBomXJList.ToList();

        //    }
        //    return new List<BomContrastiveOtherDto>();
        //}


        /// <summary>
        /// 查询分析比对
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<BomAnalysisDto>> GetBomAnalysis(GetBomAnalysisContentListInput input)
        {
            if (input.XJName == null) { return new PagedResultDto<BomAnalysisDto>(); }
            var loadQuery = _loadPieceRepository.GetAll();
            string[] str = input.XJName.Trim().Split(' ');//add 12/3
            string xjCode = "";
            string xjName = "";
            if (str.Length > 1)
            {
                xjCode = str[1];
                xjName = str[0];
            }
            var bomdesignDetailLists = _bomDesignDetailrepository.GetAll().ToList();
            var collectDetailLists = _collectBomManagerChildRepository.GetAll().ToList();
            //查询制造bom相关内容
            //型号发次
            var moduleLotNo = _bopDescRepository.FirstOrDefaultAsync(t => t.ModelId == input.ModelId&& t.LotNoId ==input.SendTimeId).Result;
            if (moduleLotNo == null) 
            {
                throw new UserFriendlyException("bop没有对应的型号发次，无法对比，请检查规程");
            }
            var coreNode = _bopNodeRepository.GetAll().Where(d =>  d.ParentId == moduleLotNo.Id  &&d.NodeDrawingNo ==xjCode && d.NodeName ==xjName).FirstOrDefault();
            if (coreNode != null)
            {
                var techQuery = _bopNodeRepository.GetAll().Where(d => d.NodeCode.StartsWith(coreNode.NodeCode) && d.BopDescId == coreNode.BopDescId).Where(d => d.TechniqueEntityId != null).Select(d => d.TechniqueEntityId).ToList();
                var workIds = _workStepEntityRepository.GetAll().Where(d => techQuery.Contains(d.TechniqueEntityId)).Select(d => d.Id);
                //找出所有的装入件
                var loadList = (from cc in loadQuery
                                join mm in workIds on cc.WorkStepEntityId equals mm
                                select cc).AsNoTracking();


                var queryBomAnalysis = from queryBomAnalysisAll in (from queryBomMakeData in loadList.ToList()
                                                                        //group queryBomData by new { queryBomData.Code, queryBomData.TechniqueNumber, queryBomData.ProductCode, queryBomData.LotNo } into groupbyData
                                                                    group queryBomMakeData by new { queryBomMakeData.Code, queryBomMakeData.Name, queryBomMakeData.MaterialsContentId } into groupByData
                                                                    select new
                                                                    {
                                                                        MaterialNo = _storeFunctionsAppService.GetStandardDrawingNoDispose(groupByData.Key.Code).ProductDrawingNo + " " + _storeFunctionsAppService.GetStandardDrawingNoDispose(groupByData.Key.Code).Module,
                                                                        //IsInt(groupByData.Key.Code.Split(' ')[0].Substring(groupByData.Key.Code.Split(' ')[0].LastIndexOf("-") + 1))&& (groupByData.Key.Code.Split(' ')[0].Substring(groupByData.Key.Code.Split(' ')[0].LastIndexOf("-") + 1)).Length == 4? groupByData.Key.Code.Replace("-" + groupByData.Key.Code.Split(' ')[0].Substring(groupByData.Key.Code.Split(' ')[0].LastIndexOf("-") + 1), ""): groupByData.Key.Code,
                                                                        Unit1BomMake = groupByData.Sum(t => t.Number),
                                                                        Name = groupByData.Key.Name,
                                                                        MaterialsContentId = groupByData.Key.MaterialsContentId
                                                                    })
                                       select new BomAnalysisDto
                                       {
                                           MaterialNo = queryBomAnalysisAll.MaterialNo.Trim(),
                                           Unit1BomMake = queryBomAnalysisAll.Unit1BomMake,
                                           MaterialName = queryBomAnalysisAll.Name,
                                           MaterialId = queryBomAnalysisAll.MaterialsContentId

                                           //MaterialId = mt.Id
                                       };
                var unionBopload = from bomAnlysi in queryBomAnalysis
                                   join materilsContent in _materialsContentrepository.GetAll() on bomAnlysi.MaterialId equals materilsContent.Id
                                   join unionMaterialsNode in _materialsNoderepository.GetAll() on materilsContent.MaterialsNodeId equals unionMaterialsNode.Id
                                   join unionSysCode in _sysCodeRepository.GetAll() on materilsContent.UnitId equals unionSysCode.Id
                                   select new BomAnalysisDto
                                   {
                                       MaterialNo = bomAnlysi.MaterialNo.Trim(),
                                       Unit1BomMake = bomAnlysi.Unit1BomMake,
                                       MaterialName = bomAnlysi.MaterialName,
                                       MaterialId = bomAnlysi.MaterialId,
                                       MaterialProperty = Enum.Parse(typeof(MaterialsEnum.MaterialProperty), materilsContent.MaterialProperty.ToString()).ToString(),
                                       MaterialTypeName = unionMaterialsNode.MaterialTypeName,
                                       Unit = unionSysCode.Description

                                       //MaterialId = mt.Id
                                   };
                if (unionBopload == null && unionBopload.Count() == 0)
                {
                    throw new UserFriendlyException("没有对应的装入件数据，无法对比，请检查规程");
                }

                //找出所有的规程  （找到所有规程 能找到所有的装入件、contains）


                //根据bom版本查询设计bom相关数据 t => t.VersionNo == input.BomVersion &&t=>t.Status == (int)DocumentStatus.已发布 &&
                //找出芯级下发布的部套
                var bomDesignList = (from bomDesign_List in _bomDesignrepository.GetAll().Where(t => t.SendTimeId == input.SendTimeId && t.ModuleId == input.ModelId)
                                    select bomDesign_List).ToList();

                var xjList = (from queryBomDesignDetailContent in bomdesignDetailLists.Where(t => t.ParentId == Guid.Empty)
                             join bomDesignListContent in bomDesignList on queryBomDesignDetailContent.BomDesignId equals bomDesignListContent.Id
                             join queryMaterListContent in _materialsContentrepository.GetAll() on queryBomDesignDetailContent.MaterialId equals queryMaterListContent.Id into mls
                             from qy in mls.DefaultIfEmpty()
                             where qy.MaterialName == xjName && qy.MaterialNo == xjCode
                             select queryBomDesignDetailContent).ToList();
                //add 12/3
                if (xjList.Count == 0)
                {
                    throw new UserFriendlyException("没有找到对应的芯级信息");
                }
                //找出芯级下已发布的部套
                var btList = (from queryBomBt in bomdesignDetailLists.Where(t => t.LevelRelationship.StartsWith(xjList[0].LevelRelationship) && t.Status == DocumentStatus.已发布 && t.MaterialProperty == (int)MaterialsEnum.MaterialProperty.部件 && t.BomDesignId == bomDesignList[0].Id)
                             select queryBomBt).ToList();
                //找出所有已发布部套下的零件(因先汇总再对比 所以不需要取组件 因为汇总已经汇总到零件上了)
                //var materilsList  = from queryMaterilsList in bomdesignDetailLists.Where(t=>t.LevelRelationship)
                List<CollectBomManagerChild> bomDesignDetailsList = new List<CollectBomManagerChild>();
                foreach (BomDesignDetail designDetail in btList)
                {
                    //找出每个部套下的零件
                    var materilsList = from queryMaterilsList in collectDetailLists.Where(t => t.LevelRelationship.StartsWith(designDetail.LevelRelationship) && t.MaterialProperty == (int)MaterialsEnum.MaterialProperty.零件 && t.BomDesignId == bomDesignList[0].Id)
                                       select new CollectBomManagerChild
                                       {
                                           BomDosageTotalAmount = queryMaterilsList.BomDosageTotalAmount,
                                           MaterialId = queryMaterilsList.MaterialId,
                                           LevelRelationship = queryMaterilsList.LevelRelationship
                                       }
                                        ;
                    bomDesignDetailsList.AddRange(materilsList);
                }
                if (bomDesignDetailsList.Count == 0)
                {
                    throw new UserFriendlyException("设计bom没有对应的物料信息，请检查");
                }
                //var btLevel = string.Join(",", btList.Select(t => t.LevelRelationship));
                //// **根据芯级找出所有部套下的所有组件和零件
                //var componentDatas = _bomDesignDetailrepository.GetAll().Where(t => btLevel.Contains(t.LevelRelationship) && t.BomDesignId == bomDesignList.ToList()[0].Id && t.MaterialProperty == (int)MaterialsEnum.MaterialProperty.零件);
          
                var queryBomDesign = from queryBomDataAlls in (
                                                                                from bomDesignDetail in bomDesignDetailsList.AsQueryable()
                                                                                join queryMaterListContent in _materialsContentrepository.GetAll() on bomDesignDetail.MaterialId equals queryMaterListContent.Id

                                                                                select new
                                                                                {
                                                                                    MaterialNoBomDesign = queryMaterListContent.MaterialNo,
                                                                                    Model = queryMaterListContent.Model,
                                                                                    bomDesignDetail.BomDosageTotalAmount,
                                                                                    YearsNo = bomDesignDetail.YearsNo,
                                                                                    MaterialsName = queryMaterListContent.MaterialName
                                                                                }
                                                                )
                                     group queryBomDataAlls by new { queryBomDataAlls.MaterialNoBomDesign, queryBomDataAlls.Model } into groupByData
                                     select new
                                     {
                                         MaterialNo = groupByData.Key.MaterialNoBomDesign,
                                         Model = groupByData.Key.Model,
                                         Unit1BomDosage = groupByData.Sum(t => Convert.ToInt32(t.BomDosageTotalAmount)),
                                     };

                var queryBomDesignCalc = from bomListCalc in queryBomDesign
                                         join MaterialsContentList in _materialsContentrepository.GetAll() on
                                         new { no = bomListCalc.MaterialNo, mod = bomListCalc.Model } equals new { no = MaterialsContentList.MaterialNo, mod = MaterialsContentList.Model }
                                         join unionMaterialsNode in _materialsNoderepository.GetAll() on MaterialsContentList.MaterialsNodeId equals unionMaterialsNode.Id
                                         join unionSysCode in _sysCodeRepository.GetAll() on MaterialsContentList.UnitId equals unionSysCode.Id
                                         select new BomAnalysisDto
                                         {
                                             MaterialNo = bomListCalc.MaterialNo + (bomListCalc.Model == "-" ? "" : " ") + (bomListCalc.Model == "-" ? "" : bomListCalc.Model),
                                             Model = bomListCalc.Model == "-" ? "" : bomListCalc.Model,
                                             Unit1BomDosage = bomListCalc.Unit1BomDosage,
                                             MaterialId = MaterialsContentList.Id,
                                             MaterialName= MaterialsContentList.MaterialName,
                                             MaterialTypeName = unionMaterialsNode.MaterialTypeName,
                                             Unit = unionSysCode.Description,
                                             MaterialProperty = Enum.Parse(typeof(MaterialsEnum.MaterialProperty), MaterialsContentList.MaterialProperty.ToString()).ToString(),
                                         };
                //找出差集
                // IQueryable<BomAnalysisDto> onlyInFirstSet = queryBomAnalysis.Except(queryBomAnalysis);
                //找出BomAnalysis中的数据去除 与BomDesign共有数据
                var differenceBomAnalysisList = unionBopload.Except(queryBomDesignCalc.ToList()).ToList();
                //找出BomDesign中的数据 去除与BomAnalysisList共有数据
                var differenceBomDesignList = queryBomDesignCalc.Except(unionBopload.ToList());
                var unionDifferenceList = differenceBomDesignList.Union(differenceBomAnalysisList);
                //unionDifferenceList.ToList().ForEach(q => 
                //{
                //    q.Difference = (q.Unit1BomDosage - q.Unit1BomMake);
                //});
                //var cjList = unionDifferenceList;
                //级联差集数据
                //var union = from unionDifferenceData in unionDifferenceList
                //            join unionMaterialsContentData2 in _materialsContentrepository.GetAll() on unionDifferenceData.MaterialNo equals unionMaterialsContentData2.MaterialNo + ' ' + unionMaterialsContentData2.Model
                //            into mater
                //            from mts in mater.DefaultIfEmpty()
                //            join unionMaterialsNode in _materialsNoderepository.GetAll() on mts == null ? Guid.Empty : mts.MaterialsNodeId equals unionMaterialsNode == null ? Guid.Empty : unionMaterialsNode.Id
                //                              into matn
                //            from mtsn in matn.DefaultIfEmpty()
                //            select new BomAnalysisDto
                //            {
                //                MaterialNo = mts == null ? unionDifferenceData.MaterialNo : mts.MaterialNo,
                //                MaterialName = mts == null ? unionDifferenceData.MaterialName : mts.MaterialName,
                //                Model = mts == null ? unionDifferenceData.Model : mts.Model
                //            };


                var queryUnionDifferenceList = from unionDifferenceData in unionDifferenceList
                                               join unionMaterialsContentData2 in _materialsContentrepository.GetAll() on unionDifferenceData.MaterialNo equals unionMaterialsContentData2.MaterialNo + ' ' + unionMaterialsContentData2.Model into mater
                                               from mts in mater.DefaultIfEmpty()
                                               join unionMaterialsNode in _materialsNoderepository.GetAll() on mts == null ? Guid.Empty : mts.MaterialsNodeId equals unionMaterialsNode == null ? Guid.Empty : unionMaterialsNode.Id into matn
                                               from mtsn in matn.DefaultIfEmpty()
                                               join unionSysCode in _sysCodeRepository.GetAll() on mts == null ? Guid.Empty : mts.UnitId equals unionSysCode == null ? Guid.Empty : unionSysCode.Id
                                               into joinmt
                                               from mt in joinmt.DefaultIfEmpty()
                                               select new BomAnalysisDto
                                               {
                                                   MaterialNo = mts == null ? unionDifferenceData.MaterialNo : mts.MaterialNo,
                                                   MaterialName = mts == null ? unionDifferenceData.MaterialName : mts.MaterialName,
                                                   Model = mts == null ? unionDifferenceData.Model : mts.Model,
                                                   //MaterialProperty = Enum.Parse(typeof(MaterialsEnum.MaterialProperty), unionMaterialsContentData2.MaterialProperty.ToString()).ToString(),
                                                   MaterialTypeName = mtsn == null ? unionDifferenceData.MaterialTypeName : mtsn.MaterialTypeName,
                                                   Unit = mt == null ? unionDifferenceData.Unit : mt.Code,
                                                   Unit1BomMake = unionDifferenceData.Unit1BomMake,
                                                   Unit1BomDosage = unionDifferenceData.Unit1BomDosage,
                                                   Difference = unionDifferenceData.Unit1BomDosage - unionDifferenceData.Unit1BomMake,
                                                   MaterialId = unionDifferenceData.MaterialId,
                                                   MaterialProperty = unionDifferenceData==null ? Enum.Parse(typeof(MaterialsEnum.MaterialProperty), mts.MaterialProperty.ToString()).ToString(): unionDifferenceData.MaterialProperty
                                               };
                //数据共有比对 + '-' + queryBomData.Model

                //var fadfa = from unionData in (from queryBomMakeData in queryBomAnalysis
                //                               join queryBomData in queryBomDesignCalc on queryBomMakeData.MaterialNo equals queryBomData.MaterialNo
                //                               select new
                //                               {
                //                                   MaterialId = queryBomData.MaterialId,
                //                                   MaterialNo = queryBomMakeData.MaterialNo,
                //                                   Unit1BomMake = queryBomMakeData.Unit1BomMake,
                //                                   Unit1BomDosage = queryBomData.Unit1BomDosage,
                //                                   Difference = queryBomData.Unit1BomDosage - queryBomMakeData.Unit1BomMake,
                //                                   Name = queryBomMakeData.MaterialName
                //                               })
                //            join unionMaterialsContentData2 in _materialsContentrepository.GetAll() on unionData.MaterialId equals unionMaterialsContentData2.Id
                //            select unionData;
   
                var queryUnionData = from unionData in (from queryBomMakeData in unionBopload
                                                        join queryBomData in queryBomDesignCalc on queryBomMakeData.MaterialNo equals queryBomData.MaterialNo
                                                        select new
                                                        {
                                                            MaterialId = queryBomData.MaterialId,
                                                            MaterialNo = queryBomMakeData.MaterialNo,
                                                            Unit1BomMake = queryBomMakeData.Unit1BomMake,
                                                            Unit1BomDosage = queryBomData.Unit1BomDosage,
                                                            Difference = queryBomData.Unit1BomDosage - queryBomMakeData.Unit1BomMake,
                                                            Name = queryBomMakeData.MaterialName,
                                                            MaterialProperty = queryBomData.MaterialProperty,
                                                            Unit = queryBomData.Unit,
                                                            Module = queryBomData.Model
                                                        })
                                     join unionMaterialsContentData2 in _materialsContentrepository.GetAll() on unionData.MaterialId equals unionMaterialsContentData2.Id
                                     join unionMaterialsNode in _materialsNoderepository.GetAll() on unionMaterialsContentData2.MaterialsNodeId equals unionMaterialsNode.Id
                                     join unionSysCode in _sysCodeRepository.GetAll() on unionMaterialsContentData2.UnitId equals unionSysCode.Id
                                     into joinmt
                                     from mt in joinmt.DefaultIfEmpty()
                                     select new BomAnalysisDto
                                     {
                                         MaterialNo = unionMaterialsContentData2.MaterialNo,
                                         MaterialName = unionMaterialsContentData2.MaterialName,
                                         Model = unionMaterialsContentData2 == null
                                    ? unionData.Module : unionMaterialsContentData2.Model,
                                         MaterialProperty = unionData.MaterialProperty,
                                         MaterialTypeName = unionMaterialsNode.MaterialTypeName,
                                         Unit =  unionData.Unit,
                                         Unit1BomMake = unionData.Unit1BomMake,
                                         Unit1BomDosage = unionData.Unit1BomDosage,
                                         Difference = unionData.Difference
                                     };
                var differencet = queryUnionDifferenceList.Except(queryUnionData.ToList());
                var ReturnData = differencet.Union(queryUnionData.ToList());
                //var returnData = from returnDataLiat in ReturnData
                //                 select new BomAnalysisDto
                //                 {
                //                     MaterialNo = returnDataLiat.MaterialNo,
                //                     MaterialName = returnDataLiat.MaterialName,
                //                     Model = returnDataLiat.Model,
                //                     MaterialProperty = Enum.Parse(typeof(MaterialsEnum.MaterialProperty), returnDataLiat.MaterialProperty.ToString()).ToString(),
                //                     MaterialTypeName = returnDataLiat.MaterialTypeName,
                //                     Unit = returnDataLiat.Unit,
                //                     Unit1BomMake = returnDataLiat.Unit1BomMake,
                //                     Unit1BomDosage = returnDataLiat.Unit1BomDosage,
                //                     Difference = returnDataLiat.Difference
                //                 };

                if (ReturnData != null)
                {
                    try
                    {
                        var collectBomContents = ReturnData.OrderBy(d => d.MaterialNo).OrderBy(s => s.Model).PageBy(input).ToList();
                        return new PagedResultDto<BomAnalysisDto>(ReturnData.Count(), collectBomContents);
                    }
                    catch (Exception ex)
                    {
                        throw new UserFriendlyException("系统内部错误,请重试或联系管理员");
                    }
                }
                else
                {
                    throw new UserFriendlyException("异常,请重试或联系管理员");
                }
            }
            else 
            {
                throw new UserFriendlyException("芯级图号名称不统一或无数据");
            }
        }
        /// <summary>
        /// 导出比对分析主页内容
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public string GetBomAnalysisToExcel(GetBomAnalysisContentListInput input)
        {
            if (input.XJName == null) { return null; }
            var loadQuery = _loadPieceRepository.GetAll();
            string[] str = input.XJName.Trim().Split(' ');//add 12/3
            string xjCode = "";
            string xjName = "";
            if (str.Length > 1)
            {
                xjCode = str[1];
                xjName = str[0];
            }
            var bomdesignDetailLists = _bomDesignDetailrepository.GetAll().ToList();
            var collectDetailLists = _collectBomManagerChildRepository.GetAll().ToList();
            //查询制造bom相关内容
            //型号发次
            var moduleLotNo = _bopDescRepository.FirstOrDefaultAsync(t => t.ModelId == input.ModelId && t.LotNoId == input.SendTimeId).Result;
            if (moduleLotNo == null)
            {
                throw new UserFriendlyException("bop没有对应的型号发次，无法对比，请检查规程");
            }
            var coreNode = _bopNodeRepository.GetAll().Where(d => d.ParentId == moduleLotNo.Id && d.NodeDrawingNo == xjCode && d.NodeName == xjName).FirstOrDefault();
            if (coreNode != null)
            {
                var techQuery = _bopNodeRepository.GetAll().Where(d => d.NodeCode.StartsWith(coreNode.NodeCode) && d.BopDescId == coreNode.BopDescId).Where(d => d.TechniqueEntityId != null).Select(d => d.TechniqueEntityId).ToList();
                var workIds = _workStepEntityRepository.GetAll().Where(d => techQuery.Contains(d.TechniqueEntityId)).Select(d => d.Id);
                //找出所有的装入件
                var loadList = (from cc in loadQuery
                                join mm in workIds on cc.WorkStepEntityId equals mm
                                select cc).AsNoTracking();


                var queryBomAnalysis = from queryBomAnalysisAll in (from queryBomMakeData in loadList.ToList()
                                                                        //group queryBomData by new { queryBomData.Code, queryBomData.TechniqueNumber, queryBomData.ProductCode, queryBomData.LotNo } into groupbyData
                                                                    group queryBomMakeData by new { queryBomMakeData.Code, queryBomMakeData.Name, queryBomMakeData.MaterialsContentId } into groupByData
                                                                    select new
                                                                    {
                                                                        MaterialNo = _storeFunctionsAppService.GetStandardDrawingNoDispose(groupByData.Key.Code).ProductDrawingNo + " " + _storeFunctionsAppService.GetStandardDrawingNoDispose(groupByData.Key.Code).Module,
                                                                        //IsInt(groupByData.Key.Code.Split(' ')[0].Substring(groupByData.Key.Code.Split(' ')[0].LastIndexOf("-") + 1))&& (groupByData.Key.Code.Split(' ')[0].Substring(groupByData.Key.Code.Split(' ')[0].LastIndexOf("-") + 1)).Length == 4? groupByData.Key.Code.Replace("-" + groupByData.Key.Code.Split(' ')[0].Substring(groupByData.Key.Code.Split(' ')[0].LastIndexOf("-") + 1), ""): groupByData.Key.Code,
                                                                        Unit1BomMake = groupByData.Sum(t => t.Number),
                                                                        Name = groupByData.Key.Name,
                                                                        MaterialsContentId = groupByData.Key.MaterialsContentId
                                                                    })
                                       select new BomAnalysisDto
                                       {
                                           MaterialNo = queryBomAnalysisAll.MaterialNo.Trim(),
                                           Unit1BomMake = queryBomAnalysisAll.Unit1BomMake,
                                           MaterialName = queryBomAnalysisAll.Name,
                                           MaterialId = queryBomAnalysisAll.MaterialsContentId

                                           //MaterialId = mt.Id
                                       };
                var unionBopload = from bomAnlysi in queryBomAnalysis
                                   join materilsContent in _materialsContentrepository.GetAll() on bomAnlysi.MaterialId equals materilsContent.Id
                                   join unionMaterialsNode in _materialsNoderepository.GetAll() on materilsContent.MaterialsNodeId equals unionMaterialsNode.Id
                                   join unionSysCode in _sysCodeRepository.GetAll() on materilsContent.UnitId equals unionSysCode.Id
                                   select new BomAnalysisDto
                                   {
                                       MaterialNo = bomAnlysi.MaterialNo.Trim(),
                                       Unit1BomMake = bomAnlysi.Unit1BomMake,
                                       MaterialName = bomAnlysi.MaterialName,
                                       MaterialId = bomAnlysi.MaterialId,
                                       MaterialProperty = Enum.Parse(typeof(MaterialsEnum.MaterialProperty), materilsContent.MaterialProperty.ToString()).ToString(),
                                       MaterialTypeName = unionMaterialsNode.MaterialTypeName,
                                       Unit = unionSysCode.Description

                                       //MaterialId = mt.Id
                                   };
                if (unionBopload == null && unionBopload.Count() == 0)
                {
                    throw new UserFriendlyException("没有对应的装入件数据，无法对比，请检查规程");
                }

                //找出所有的规程  （找到所有规程 能找到所有的装入件、contains）


                //根据bom版本查询设计bom相关数据 t => t.VersionNo == input.BomVersion &&t=>t.Status == (int)DocumentStatus.已发布 &&
                //找出芯级下发布的部套
                var bomDesignList = (from bomDesign_List in _bomDesignrepository.GetAll().Where(t => t.SendTimeId == input.SendTimeId && t.ModuleId == input.ModelId)
                                     select bomDesign_List).ToList();

                var xjList = (from queryBomDesignDetailContent in bomdesignDetailLists.Where(t => t.ParentId == Guid.Empty)
                              join bomDesignListContent in bomDesignList on queryBomDesignDetailContent.BomDesignId equals bomDesignListContent.Id
                              join queryMaterListContent in _materialsContentrepository.GetAll() on queryBomDesignDetailContent.MaterialId equals queryMaterListContent.Id into mls
                              from qy in mls.DefaultIfEmpty()
                              where qy.MaterialName == xjName && qy.MaterialNo == xjCode
                              select queryBomDesignDetailContent).ToList();
                //add 12/3
                if (xjList.Count() == 0)
                {
                    throw new UserFriendlyException("没有找到对应的芯级信息");
                }
                //找出芯级下已发布的部套
                var btList = (from queryBomBt in bomdesignDetailLists.Where(t => t.LevelRelationship.StartsWith(xjList[0].LevelRelationship) && t.Status == DocumentStatus.已发布 && t.MaterialProperty == (int)MaterialsEnum.MaterialProperty.部件 && t.BomDesignId == bomDesignList[0].Id)
                             select queryBomBt).ToList();
                //找出所有已发布部套下的零件(因先汇总再对比 所以不需要取组件 因为汇总已经汇总到零件上了)
                //var materilsList  = from queryMaterilsList in bomdesignDetailLists.Where(t=>t.LevelRelationship)
                List<CollectBomManagerChild> bomDesignDetailsList = new List<CollectBomManagerChild>();
                foreach (BomDesignDetail designDetail in btList)
                {
                    //找出每个部套下的零件
                    var materilsList = from queryMaterilsList in collectDetailLists.Where(t => t.LevelRelationship.StartsWith(designDetail.LevelRelationship) && t.MaterialProperty == (int)MaterialsEnum.MaterialProperty.零件 && t.BomDesignId == bomDesignList[0].Id)
                                       select new CollectBomManagerChild
                                       {
                                           BomDosageTotalAmount = queryMaterilsList.BomDosageTotalAmount,
                                           MaterialId = queryMaterilsList.MaterialId,
                                           LevelRelationship = queryMaterilsList.LevelRelationship
                                       }
                                        ;
                    bomDesignDetailsList.AddRange(materilsList);
                }
                if (bomDesignDetailsList.Count == 0)
                {
                    throw new UserFriendlyException("设计bom没有对应的物料信息，请检查");
                }
                //var btLevel = string.Join(",", btList.Select(t => t.LevelRelationship));
                //// **根据芯级找出所有部套下的所有组件和零件
                //var componentDatas = _bomDesignDetailrepository.GetAll().Where(t => btLevel.Contains(t.LevelRelationship) && t.BomDesignId == bomDesignList.ToList()[0].Id && t.MaterialProperty == (int)MaterialsEnum.MaterialProperty.零件);

                var queryBomDesign = from queryBomDataAlls in (
                                                                                from bomDesignDetail in bomDesignDetailsList.AsQueryable()
                                                                                join queryMaterListContent in _materialsContentrepository.GetAll() on bomDesignDetail.MaterialId equals queryMaterListContent.Id

                                                                                select new
                                                                                {
                                                                                    MaterialNoBomDesign = queryMaterListContent.MaterialNo,
                                                                                    Model = queryMaterListContent.Model,
                                                                                    bomDesignDetail.BomDosageTotalAmount,
                                                                                    YearsNo = bomDesignDetail.YearsNo,
                                                                                    MaterialsName = queryMaterListContent.MaterialName
                                                                                }
                                                                )
                                     group queryBomDataAlls by new { queryBomDataAlls.MaterialNoBomDesign, queryBomDataAlls.Model } into groupByData
                                     select new
                                     {
                                         MaterialNo = groupByData.Key.MaterialNoBomDesign,
                                         Model = groupByData.Key.Model,
                                         Unit1BomDosage = groupByData.Sum(t => Convert.ToInt32(t.BomDosageTotalAmount)),
                                     };

                var queryBomDesignCalc = from bomListCalc in queryBomDesign
                                         join MaterialsContentList in _materialsContentrepository.GetAll() on
                                         new { no = bomListCalc.MaterialNo, mod = bomListCalc.Model } equals new { no = MaterialsContentList.MaterialNo, mod = MaterialsContentList.Model }
                                         join unionMaterialsNode in _materialsNoderepository.GetAll() on MaterialsContentList.MaterialsNodeId equals unionMaterialsNode.Id
                                         join unionSysCode in _sysCodeRepository.GetAll() on MaterialsContentList.UnitId equals unionSysCode.Id
                                         select new BomAnalysisDto
                                         {
                                             MaterialNo = bomListCalc.MaterialNo + (bomListCalc.Model == "-" ? "" : " ") + (bomListCalc.Model == "-" ? "" : bomListCalc.Model),
                                             Model = bomListCalc.Model == "-" ? "" : bomListCalc.Model,
                                             Unit1BomDosage = bomListCalc.Unit1BomDosage,
                                             MaterialId = MaterialsContentList.Id,
                                             MaterialName = MaterialsContentList.MaterialName,
                                             MaterialTypeName = unionMaterialsNode.MaterialTypeName,
                                             Unit = unionSysCode.Description,
                                             MaterialProperty = Enum.Parse(typeof(MaterialsEnum.MaterialProperty), MaterialsContentList.MaterialProperty.ToString()).ToString(),
                                         };
                //找出差集
                // IQueryable<BomAnalysisDto> onlyInFirstSet = queryBomAnalysis.Except(queryBomAnalysis);
                //找出BomAnalysis中的数据去除 与BomDesign共有数据
                var differenceBomAnalysisList = unionBopload.Except(queryBomDesignCalc.ToList()).ToList();
                //找出BomDesign中的数据 去除与BomAnalysisList共有数据
                var differenceBomDesignList = queryBomDesignCalc.Except(unionBopload.ToList());
                var unionDifferenceList = differenceBomDesignList.Union(differenceBomAnalysisList);
                //unionDifferenceList.ToList().ForEach(q => 
                //{
                //    q.Difference = (q.Unit1BomDosage - q.Unit1BomMake);
                //});
                //var cjList = unionDifferenceList;
                //级联差集数据
                //var union = from unionDifferenceData in unionDifferenceList
                //            join unionMaterialsContentData2 in _materialsContentrepository.GetAll() on unionDifferenceData.MaterialNo equals unionMaterialsContentData2.MaterialNo + ' ' + unionMaterialsContentData2.Model
                //            into mater
                //            from mts in mater.DefaultIfEmpty()
                //            join unionMaterialsNode in _materialsNoderepository.GetAll() on mts == null ? Guid.Empty : mts.MaterialsNodeId equals unionMaterialsNode == null ? Guid.Empty : unionMaterialsNode.Id
                //                              into matn
                //            from mtsn in matn.DefaultIfEmpty()
                //            select new BomAnalysisDto
                //            {
                //                MaterialNo = mts == null ? unionDifferenceData.MaterialNo : mts.MaterialNo,
                //                MaterialName = mts == null ? unionDifferenceData.MaterialName : mts.MaterialName,
                //                Model = mts == null ? unionDifferenceData.Model : mts.Model
                //            };


                var queryUnionDifferenceList = from unionDifferenceData in unionDifferenceList
                                               join unionMaterialsContentData2 in _materialsContentrepository.GetAll() on unionDifferenceData.MaterialNo equals unionMaterialsContentData2.MaterialNo + ' ' + unionMaterialsContentData2.Model into mater
                                               from mts in mater.DefaultIfEmpty()
                                               join unionMaterialsNode in _materialsNoderepository.GetAll() on mts == null ? Guid.Empty : mts.MaterialsNodeId equals unionMaterialsNode == null ? Guid.Empty : unionMaterialsNode.Id into matn
                                               from mtsn in matn.DefaultIfEmpty()
                                               join unionSysCode in _sysCodeRepository.GetAll() on mts == null ? Guid.Empty : mts.UnitId equals unionSysCode == null ? Guid.Empty : unionSysCode.Id
                                               into joinmt
                                               from mt in joinmt.DefaultIfEmpty()
                                               select new BomAnalysisDto
                                               {
                                                   MaterialNo = mts == null ? unionDifferenceData.MaterialNo : mts.MaterialNo,
                                                   MaterialName = mts == null ? unionDifferenceData.MaterialName : mts.MaterialName,
                                                   Model = mts == null ? unionDifferenceData.Model : mts.Model,
                                                   //MaterialProperty = Enum.Parse(typeof(MaterialsEnum.MaterialProperty), unionMaterialsContentData2.MaterialProperty.ToString()).ToString(),
                                                   MaterialTypeName = mtsn == null ? unionDifferenceData.MaterialTypeName : mtsn.MaterialTypeName,
                                                   Unit = mt == null ? unionDifferenceData.Unit : mt.Code,
                                                   Unit1BomMake = unionDifferenceData.Unit1BomMake,
                                                   Unit1BomDosage = unionDifferenceData.Unit1BomDosage,
                                                   Difference = unionDifferenceData.Unit1BomDosage - unionDifferenceData.Unit1BomMake,
                                                   MaterialId = unionDifferenceData.MaterialId,
                                                   MaterialProperty = unionDifferenceData == null ? Enum.Parse(typeof(MaterialsEnum.MaterialProperty), mts.MaterialProperty.ToString()).ToString() : unionDifferenceData.MaterialProperty
                                               };
                //数据共有比对 + '-' + queryBomData.Model

                //var fadfa = from unionData in (from queryBomMakeData in queryBomAnalysis
                //                               join queryBomData in queryBomDesignCalc on queryBomMakeData.MaterialNo equals queryBomData.MaterialNo
                //                               select new
                //                               {
                //                                   MaterialId = queryBomData.MaterialId,
                //                                   MaterialNo = queryBomMakeData.MaterialNo,
                //                                   Unit1BomMake = queryBomMakeData.Unit1BomMake,
                //                                   Unit1BomDosage = queryBomData.Unit1BomDosage,
                //                                   Difference = queryBomData.Unit1BomDosage - queryBomMakeData.Unit1BomMake,
                //                                   Name = queryBomMakeData.MaterialName
                //                               })
                //            join unionMaterialsContentData2 in _materialsContentrepository.GetAll() on unionData.MaterialId equals unionMaterialsContentData2.Id
                //            select unionData;

                var queryUnionData = from unionData in (from queryBomMakeData in unionBopload
                                                        join queryBomData in queryBomDesignCalc on queryBomMakeData.MaterialNo equals queryBomData.MaterialNo
                                                        select new
                                                        {
                                                            MaterialId = queryBomData.MaterialId,
                                                            MaterialNo = queryBomMakeData.MaterialNo,
                                                            Unit1BomMake = queryBomMakeData.Unit1BomMake,
                                                            Unit1BomDosage = queryBomData.Unit1BomDosage,
                                                            Difference = queryBomData.Unit1BomDosage - queryBomMakeData.Unit1BomMake,
                                                            Name = queryBomMakeData.MaterialName,
                                                            MaterialProperty = queryBomData.MaterialProperty,
                                                            Unit = queryBomData.Unit,
                                                            Module = queryBomData.Model
                                                        })
                                     join unionMaterialsContentData2 in _materialsContentrepository.GetAll() on unionData.MaterialId equals unionMaterialsContentData2.Id
                                     join unionMaterialsNode in _materialsNoderepository.GetAll() on unionMaterialsContentData2.MaterialsNodeId equals unionMaterialsNode.Id
                                     join unionSysCode in _sysCodeRepository.GetAll() on unionMaterialsContentData2.UnitId equals unionSysCode.Id
                                     into joinmt
                                     from mt in joinmt.DefaultIfEmpty()
                                     select new BomAnalysisDto
                                     {
                                         MaterialNo = unionMaterialsContentData2.MaterialNo,
                                         MaterialName = unionMaterialsContentData2.MaterialName,
                                         Model = unionMaterialsContentData2 == null
                                    ? unionData.Module : unionMaterialsContentData2.Model,
                                         MaterialProperty = unionData.MaterialProperty,
                                         MaterialTypeName = unionMaterialsNode.MaterialTypeName,
                                         Unit = unionData.Unit,
                                         Unit1BomMake = unionData.Unit1BomMake,
                                         Unit1BomDosage = unionData.Unit1BomDosage,
                                         Difference = unionData.Difference
                                     };
                var differencet = queryUnionDifferenceList.Except(queryUnionData.ToList());
                var ReturnData = differencet.Union(queryUnionData.ToList());
                if (ReturnData != null)
                {
                    return _bomContrastiveToExcel.ExportToFile(ReturnData.OrderBy(d => d.MaterialNo).OrderBy(s=>s.Model).ToList());
                }
            }
            return null;
        }

        public  bool IsInt(string value)
        {
            return Regex.IsMatch(value, @"^[+-]?\d*$");
        }

    }
}